package com.jxc.jxc01.grc.dao.impl;

import com.jxc.jxc01.grc.dao.IProcurementDao;
import com.jxc.jxc01.grc.entity.JDBCConn;
import com.jxc.jxc01.grc.entity.Procurement;


import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class ProcurementDaoImpl implements IProcurementDao {
    @Override
    public int addProcurement(Procurement procurement) {
        try {
//            Class.forName("com.mysql.cj.jdbc.Driver");
//            grc = DriverManager.getConnection("jdbc:mysql://172.30.2.4:3306/jxc",
//                    "grc", "123456");
            String sql = "INSERT INTO procurement (purchaseId,purchaseName,goodsName,classs,purchaseSupplierName,unit,purchaseNum,unitPrice,totalPrice,purchaseTime,status,note) VALUES (?,?,?,?,?,?,?,?,?,?,?,?)";
            PreparedStatement ps = JDBCConn.connect(sql);
            ps.setInt(1, procurement.getPurchaseId());
            ps.setString(2, procurement.getPurchaseName());
            ps.setString(3, procurement.getGoodsName());
            ps.setString(4, procurement.getClasss());
            ps.setString(5, procurement.getPurchaseSupplierName());
            ps.setString(6, procurement.getUnit());
            ps.setString(7, procurement.getPurchaseNum());
            ps.setDouble(8, procurement.getUnitPrice());
            ps.setString(9, procurement.getTotalPrice());
            ps.setString(10, procurement.getPurchaseTime());
            ps.setString(11, procurement.getStatus());
            ps.setString(12, procurement.getNote());
            int i = ps.executeUpdate();
            return i;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return 0;
    }

    @Override
    public int deleteProcurement(int id) {

        try {
//            Class.forName("com.mysql.cj.jdbc.Driver");
//            grc = DriverManager.getConnection("jdbc:mysql://172.30.2.4:3306/jxc",
//                    "grc", "123456");
            String sql = "DELETE FROM procurement WHERE id=?";
            PreparedStatement ps=JDBCConn.connect(sql);
            ps.setInt(1, id);
            int i = ps.executeUpdate();
            return i;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return 0;
    }

    @Override
    public int reviseProcurement(Procurement procurement) {
        try {
//            Class.forName("com.mysql.cj.jdbc.Driver");
//            grc = DriverManager.getConnection("jdbc:mysql://172.30.2.4:3306/jxc", "grc", "123456");
            String sql = "UPDATE procurement SET purchaseId=?,purchaseName=?,goodsName=?,classs=?,purchaseSupplierName=?,unit=?,purchaseNum=?,unitPrice=?,totalPrice=?,purchaseTime=?,status=?,note=? WHERE id=?";
            PreparedStatement ps=JDBCConn.connect(sql);
            ps.setInt(1, procurement.getPurchaseId());
            ps.setString(2, procurement.getPurchaseName());
            ps.setString(3, procurement.getGoodsName());
            ps.setString(4, procurement.getClasss());
            ps.setString(5, procurement.getPurchaseSupplierName());
            ps.setString(6, procurement.getUnit());
            ps.setString(7, procurement.getPurchaseNum());
            ps.setDouble(8, procurement.getUnitPrice());
            ps.setString(9, procurement.getTotalPrice());
            ps.setString(10, procurement.getPurchaseTime());
            ps.setString(11, procurement.getStatus());
            ps.setString(12, procurement.getNote());
            ps.setInt(13,procurement.getId());
            int i = ps.executeUpdate();
            return i;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return 0;
    }

    @Override
    public List<Procurement> queryProcurement() {
        List<Procurement> procurements = new ArrayList<>();
        try {
//            Class.forName("com.mysql.cj.jdbc.Driver");
//            grc = DriverManager.getConnection("jdbc:mysql://172.30.2.4:3306/jxc", "grc", "123456");
            String sql = "SELECT * FROM procurement";
            PreparedStatement ps=JDBCConn.connect(sql);
             ResultSet rs = ps.executeQuery();
            Procurement procurement = null;
            while (rs.next()) {
                procurement = new Procurement();
                procurement.setId(rs.getInt("id"));
                procurement.setPurchaseId(rs.getInt("purchaseId"));
                procurement.setPurchaseName(rs.getString("purchaseName"));
                procurement.setGoodsName(rs.getString("goodsName"));
                procurement.setClasss(rs.getString("classs"));
                procurement.setPurchaseSupplierName(rs.getString("purchaseSupplierName"));
                procurement.setUnit(rs.getString("unit"));
                procurement.setPurchaseNum(rs.getString("purchaseNum"));
                procurement.setUnitPrice(rs.getDouble("unitPrice"));
                procurement.setTotalPrice(rs.getString("totalPrice"));
                procurement.setPurchaseTime(rs.getString("purchaseTime"));
                procurement.setStatus(rs.getString("status"));
                procurement.setNote(rs.getString("note"));
                procurements.add(procurement);
            }
        }catch (Exception e){
            e.printStackTrace();
        }
        return procurements;
    }

    @Override
    public int queryProcurementTotalCount() {
        try {
//            Class.forName("com.mysql.cj.jdbc.Driver");
//            grc = DriverManager.getConnection("jdbc:mysql://172.30.2.4:3306/jxc",
//                    "grc", "123456");
            String sql = "SELECT * FROM procurement";
            PreparedStatement ps=JDBCConn.connect(sql);
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                int row = rs.getRow();
                return row;
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return 0;
    }

    @Override
    public List<Procurement> queryProcurementBynano(int purchaseId) {
        List<Procurement> procurements = new ArrayList<>();
        try {
//            Class.forName("com.mysql.cj.jdbc.Driver");
//            grc = DriverManager.getConnection("jdbc:mysql://172.30.2.4:3306/jxc", "grc", "123456");
            String sql = "SELECT * FROM procurement WHERE purchaseId=?";
            PreparedStatement ps=JDBCConn.connect(sql);
            ps.setInt(1, purchaseId);
            ResultSet  rs = ps.executeQuery();
            Procurement procurement = null;
            while (rs.next()) {
                procurement = new Procurement();
                procurement.setId(rs.getInt("id"));
                procurement.setPurchaseId(rs.getInt("purchaseId"));
                procurement.setPurchaseName(rs.getString("purchaseName"));
                procurement.setGoodsName(rs.getString("goodsName"));
                procurement.setClasss(rs.getString("classs"));
                procurement.setPurchaseSupplierName(rs.getString("purchaseSupplierName"));
                procurement.setUnit(rs.getString("unit"));
                procurement.setPurchaseNum(rs.getString("purchaseNum"));
                procurement.setUnitPrice(rs.getDouble("unitPrice"));
                procurement.setTotalPrice(rs.getString("totalPrice"));
                procurement.setPurchaseTime(rs.getString("purchaseTime"));
                procurement.setStatus(rs.getString("status"));
                procurement.setNote(rs.getString("note"));
                procurements.add(procurement);
            }
        }catch (Exception e) {
            e.printStackTrace();
        }
        return procurements;
    }
}
